package com.dao.lzj;


import com.bean.lzj.Car;
import com.bean.lzj.TruckSpace;
import com.bean.lzj.Xiaoqu;
import com.util.lzj.DBUtil;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.PreparedStatement;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Repository

public class TruckSpaceDao {
    /**
     * 查询全部车位信息
     *
     * @return
     * @throws SQLException
     */
    public static List<TruckSpace> getCw() throws SQLException {
        List<TruckSpace> cwList = new ArrayList<>();
        String sql = "SELECT*FROM parking_place AS p,village AS v WHERE p.vid = v.vid";

        DBUtil dbUtil = new DBUtil();
        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();


        while (rs.next()) {
            TruckSpace cw = new TruckSpace();
            cw.setPid(Integer.parseInt(rs.getString("p.Pid")));
            cw.setVid(rs.getString("p.Vid"));
            cw.setTid(rs.getString("p.tid"));
            System.out.println(cw.vid);
            cw.setSid(rs.getString("p.sid"));

            cw.setParea(rs.getString("p.parea"));
            cw.setRemark(rs.getString("p.remark"));
            cw.setCaid(rs.getInt("p.caid"));
            cw.setVname(rs.getString("v.vname"));

            if (cw.sid.equals("y")) {
                cw.sid = "占用";
            } else {
                cw.sid = "空闲";
            }
            cwList.add(cw);
        }


        return cwList;
    }



    /**
     * 根据pid删除数据
     *
     * @param pid
     * @return
     */

    public static boolean deleteCwxxById(int pid) {
        DBUtil dbutil = new DBUtil();
        String sql = "delete from parking_place where pid = ?";
        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        try {
            ps.setInt(1, pid);
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 综合查询
     * @param ssxqxl
     * @param cwztxl
     * @param cwbhxl
     * @return
     * @throws SQLException
     */
    public static List<TruckSpace> getCwzhxx(String ssxqxl, String cwztxl, String cwbhxl) throws SQLException {
        System.out.println(ssxqxl);
        System.out.println(cwztxl);
        System.out.println(cwbhxl);
        TruckSpace c = new TruckSpace();
        //判断小区选没选
        String xq = ssxqxl;
        if (xq.equals("请选择")) {
            xq = null;
        }
//判断车位状态
        String zt = cwztxl;
        if (zt.equals("占用")) {
            zt = "y";
        }
        if (zt.equals("空闲")) {
            zt = "n";
        }
        if (zt.equals("请选择")) {
            zt = null;
        }
        //编写sql
        String sql = "select * from parking_place AS p,village AS v where p.vid = v.vid AND 1=1 ";
        DBUtil dbUtil = null;

        dbUtil = new DBUtil();
/**
 * 判断小区
 */

        PreparedStatement ps;
        if (xq != null && !"".equals(xq)) {

            String sql1 = "select * from village where vname=? ";


            ps = dbUtil.getPreparedStatement(sql1);
            try {
                ps.setString(1, xq);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    c.setVid(rs.getString("vid"));

                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            sql = sql + " and p.vid ='" + c.vid + "'";
            System.out.println(1);
        }
        if (zt != null && !"".equals(zt)) {
            sql = sql + " and p.sid ='" + zt + "'";
            System.out.println(zt);
        }
        if (cwbhxl != null && !"".equals(cwbhxl)) {
            sql = sql + " and p.pid ='" + cwbhxl + "'";
            System.out.println(3);
        }

        List<TruckSpace> cwList = new ArrayList<>();
        System.out.println(sql);

        ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TruckSpace cw = new TruckSpace();
            cw.setPid(Integer.parseInt(rs.getString("p.Pid")));
            cw.setVid(rs.getString("p.Vid"));
            cw.setTid(rs.getString("p.tid"));
            cw.setSid(rs.getString("p.sid"));
            cw.setVname(rs.getString("v.vname"));
            cw.setParea(rs.getString("p.parea"));
            cw.setRemark(rs.getString("p.remark"));
            cw.setCaid(rs.getInt("p.caid"));
            if (cw.sid.equals("y")) {
                cw.sid = "占用";
            } else {
                cw.sid = "空闲";
            }
            cwList.add(cw);
        }
        return cwList;
    }

    /**
     *批量 删除
     * @param fxk
     * @return
     */
    public static boolean deleteinbatchesById(int fxk) {
        TruckSpace c = new TruckSpace();
        System.out.println("-----------");
        int a = fxk;
        String b="y";
        System.out.println(fxk);
        // String rs[] = request.getParameterValues("fxk");

        DBUtil dbutil = new DBUtil();
        String sql1 = "select * from parking_place where pid=? ";

/**
 * 判断车位是否是占用的状态
 */
        PreparedStatement ps;
        ps = dbutil.getPreparedStatement(sql1);
        try {
            ps.setInt(1, a);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                c.setSid(rs.getString("sid"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (c.sid.equals("n")) {


                String sql = "delete from parking_place where pid = ?";
                ps = dbutil.getPreparedStatement(sql);
                try {
                    ps.setInt(1, Integer.parseInt(String.valueOf(a)));
                    int result = ps.executeUpdate();
                    if (result == 1) {
                        System.out.println("删除成功");
                        return true;

                    } else {
                        return false;
                    }

                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return false;
            }else {
            System.out.println("删除失败");
            return false;
        }
    }

    /**
     * 添加信息
     * @param xq 小区
     * @param cwmj 车位面积
     * @param cwlx 车位类型
     * @param whsfbz 收费标准
     * @param bz 备注
     * @return
     */
    public static boolean gettianjia(String xq, String cwmj, String cwlx, int whsfbz, String bz) {
        DBUtil dbutil = new DBUtil();
        TruckSpace c = new TruckSpace();
        String sql1 = "select * from village where vname=? ";
        PreparedStatement ps;
        ps = dbutil.getPreparedStatement(sql1);
        try {
            ps.setString(1, xq);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                c.setVid(rs.getString("vid"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql = "INSERT INTO `project2`.`parking_place`(`pid`, `vid`, `tid`, `sid`, `parea`, `remark`, `caid`) VALUES (null, ?, ?, 'n', ?, ?, ?)";
        ps = dbutil.getPreparedStatement(sql);
        try {
            ps.setString(1, c.vid);
            ps.setString(2, cwlx);
            ps.setString(3, cwmj);
            ps.setString(4, bz);
            ps.setInt(5, whsfbz);


            int result = ps.executeUpdate();
            if (result == 1) {
                System.out.println("添加成功");
                return true;

            } else {
                System.out.println("添加失败");
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    /**village
     * 小区下拉框赋值
     * @return
     */
    public static List<TruckSpace> getxq() throws SQLException {
        List<TruckSpace> xqList = new ArrayList<>();
        String sql = "select * from village";
        DBUtil dbUtil = new DBUtil();
        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TruckSpace xq = new TruckSpace();
           xq.setVname(rs.getString("vname"));
            xqList.add(xq);
        }

        return xqList;
    }

    /**
     * 修改前进行查询然后给输入框赋值
     * @param pid
     * @return
     */
    public static List<TruckSpace> getCwzhxxcx(int pid) throws SQLException {
        List<TruckSpace> cwList = new ArrayList<>();
        DBUtil dbutil = new DBUtil();
       String sql = "select * from parking_place AS p,village AS v where p.vid = v.vid AND 1=1 AND p.pid=?";

        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        ps.setString(1, String.valueOf(pid));
        ResultSet rs = ps.executeQuery();


        while (rs.next()) {
            TruckSpace cw = new TruckSpace();
            cw.setPid(Integer.parseInt(rs.getString("Pid")));
            cw.setVid(rs.getString("Vid"));
            cw.setTid(rs.getString("tid"));
            System.out.println(rs.getString("sid"));
            cw.setSid(rs.getString("sid"));
            cw.setVname(rs.getString("v.vname"));
            cw.setParea(rs.getString("parea"));
            cw.setRemark(rs.getString("remark"));
            cw.setCaid(rs.getInt("caid"));

            if (cw.sid.equals("y")) {
                cw.sid = "占用";
            } else {
                cw.sid = "空闲";
            }
            cwList.add(cw);
        }

        return cwList;
    }

    /**
     * 根据pid查看车辆信息
     * @param pid
     * @return
     */
    public List<Car> getzhxxcx(int pid) throws SQLException {
        System.out.println(pid);
        String sql ="select * from car where pid=? ";
        DBUtil dbUtil = new DBUtil();
        List<Car> cLList = new ArrayList<>();


        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ps.setString(1, String.valueOf(pid));
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            Car car = new Car();
            car.setCcid(Integer.parseInt(rs.getString("ccid")));
            car.setAid(rs.getInt("aid"));
            car.setPid(rs.getInt("pid"));
            System.out.println("---------------------------");
            car.setStopid(rs.getInt("stopid"));

            car.setCnumber(rs.getString("cnumber"));
            car.setCtel(rs.getString("ctel"));
            car.setCname(rs.getString("cname"));
            car.setCxcz(rs.getInt("cxcz"));
            cLList.add(car);
        }
        return cLList;
    }


    /**
     * 修改信息
     * @param pid
     * @param xgxq
     * @param xgcwlx
     * @param xgcwzt
     * @param xgcwmj
     * @param xgsfbz
     * @param xgbz
     * @return
     */
    public static boolean getxiugaixinxi(int pid, String xgxq, String xgcwlx, String xgcwzt, String xgcwmj, String xgsfbz, String xgbz) {
        DBUtil dbutil = new DBUtil();
        TruckSpace c = new TruckSpace();
        String sql1 = "select * from village where vname=? ";
        PreparedStatement ps;
        ps = dbutil.getPreparedStatement(sql1);
        try {
            ps.setString(1, xgxq);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                c.setVid(rs.getString("vid"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        String zt = xgcwzt;
        if (zt.equals("占用")) {
            zt = "y";
        }
        if (zt.equals("空闲")) {
            zt = "n";
        }
        System.out.println(pid);
        System.out.println(xgxq);

        String sql="UPDATE `project2`.`parking_place` SET `vid` = ?, `tid` = ?, `sid` = ?, `parea` = ?, `remark` = ?, `caid` = ? WHERE `pid` = ?";
        ps = dbutil.getPreparedStatement(sql);
        try {
            ps.setString(1, c.vid);
            ps.setString(2, xgcwlx);
            ps.setString(3, zt);
            ps.setString(4,xgcwmj);
            ps.setString(5,xgbz);
            ps.setInt(6, Integer.parseInt(xgsfbz));
            ps.setInt(7, (pid));



            int result = ps.executeUpdate();
            if (result == 1) {
                System.out.println("添加成功");
                return true;

            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


}





